package yiran.depositcumulative.data;

import org.bukkit.OfflinePlayer;
import yiran.depositcumulative.DepositCumulative;
import yiran.depositcumulative.api.DepositCumulativeAPI;
import yiran.depositcumulative.output.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;

public class DatabaseService implements DepositCumulativeAPI {
    private final DepositCumulative plugin;
    private Connection connection;

    public DatabaseService(DepositCumulative plugin) {
        this.plugin = plugin;
    }

    public void connect() {
        String dbType = plugin.getConfig().getString("database.type", "sqlite");

        try {
            if ("mysql".equalsIgnoreCase(dbType)) {
                String host = plugin.getConfig().getString("database.mysql.host");
                int port = plugin.getConfig().getInt("database.mysql.port");
                String database = plugin.getConfig().getString("database.mysql.database");
                String user = plugin.getConfig().getString("database.mysql.username");
                String pass = plugin.getConfig().getString("database.mysql.password");
                String params = plugin.getConfig().getString("database.mysql.parameters", "");

                String url = "jdbc:mysql://" + host + ":" + port + "/" + database + params;
                connection = DriverManager.getConnection(url, user, pass);
                Logger.info("已连接MySQL数据库");
            } else {
                String filename = plugin.getConfig().getString("database.sqlite.filename", "deposit_cumulative.db");
                String url = "jdbc:sqlite:" + plugin.getDataFolder().getAbsolutePath() + "/" + filename;
                connection = DriverManager.getConnection(url);
                Logger.info("已连接SQLite数据库");
            }

            createTable();
        } catch (SQLException e) {
            Logger.error("数据库连接失败: " + e.getMessage());
        }
    }

    private void createTable() {
        // 修改现有表结构
        String recordsTable = "CREATE TABLE IF NOT EXISTS deposit_records (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "player_name VARCHAR(32) NOT NULL," +
                "points INTEGER NOT NULL," +
                "deposit_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";

        // 新增累计表（使用 player_name 作为主键）
        String totalTable = "CREATE TABLE IF NOT EXISTS player_totals (" +
                "player_name VARCHAR(32) PRIMARY KEY," + // 使用玩家名称作为主键
                "total_points INTEGER NOT NULL DEFAULT 0)";

        executeUpdate(recordsTable);
        executeUpdate(totalTable);
        Logger.info("数据库表已创建/验证");
    }

    public void recordDeposit(String playerName, int points) {
        try {
            // 开启事务
            connection.setAutoCommit(false);

            // 记录明细
            String recordSql = "INSERT INTO deposit_records (player_name, points) VALUES (?, ?)";
            try (PreparedStatement stmt = connection.prepareStatement(recordSql)) {
                stmt.setString(1, playerName);
                stmt.setInt(2, points);
                stmt.executeUpdate();
            }

            // 更新累计值
            String totalSql;
            String dbType = plugin.getConfig().getString("database.type", "sqlite");
            if ("sqlite".equalsIgnoreCase(dbType)) {
                totalSql = "INSERT OR REPLACE INTO player_totals (player_name, total_points) " +
                        "VALUES (?, COALESCE((SELECT total_points FROM player_totals WHERE player_name = ?), 0) + ?)";
            } else {
                totalSql = "INSERT INTO player_totals (player_name, total_points) " +
                        "VALUES (?, ?) " +
                        "ON DUPLICATE KEY UPDATE total_points = total_points + VALUES(total_points)";
            }

            try (PreparedStatement stmt = connection.prepareStatement(totalSql)) {
                if ("sqlite".equalsIgnoreCase(dbType)) {
                    stmt.setString(1, playerName);
                    stmt.setString(2, playerName);
                    stmt.setInt(3, points);
                } else {
                    stmt.setString(1, playerName);
                    stmt.setInt(2, points);
                }
                stmt.executeUpdate();
            }

            // 提交事务
            connection.commit();
        } catch (SQLException e) {
            // 回滚事务
            try {
                if (connection != null) {
                    connection.rollback();
                }
            } catch (SQLException ex) {
                Logger.error("事务回滚失败: " + ex.getMessage());
            }

            // 记录详细错误
            String errorMsg = "充值记录失败: " + e.getMessage() +
                    "\n玩家: " + playerName +
                    "\n点数: " + points;
            Logger.error(errorMsg);
        } finally {
            // 恢复自动提交模式
            try {
                if (connection != null) {
                    connection.setAutoCommit(true);
                }
            } catch (SQLException e) {
                Logger.error("恢复自动提交失败: " + e.getMessage());
            }
        }
    }

    private void executeUpdate(String sql) {
        executeUpdate(sql, stmt -> {});
    }

    private void executeUpdate(String sql, Consumer<PreparedStatement> paramSetter) {
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            paramSetter.accept(stmt);
            stmt.executeUpdate();
        } catch (SQLException e) {
            // 添加详细的错误信息
            String errorMsg = "数据库操作失败: " + e.getMessage() +
                    "\nSQL语句: " + sql +
                    "\n错误代码: " + e.getErrorCode();
            Logger.error(errorMsg);
        }
    }

    public void close() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                Logger.error("关闭数据库连接失败: " + e.getMessage());
            }
        }
    }

    public int getTotalDeposit(String playerName) {
        String sql = "SELECT total_points FROM player_totals WHERE player_name = ?";

        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, playerName);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("total_points");
                }
            }
        } catch (SQLException e) {
            String errorMsg = "查询累计充值失败: " + e.getMessage() +
                    "\nSQL语句: " + sql +
                    "\n玩家: " + playerName;
            Logger.error(errorMsg);
            e.printStackTrace();
        }
        return 0;
    }

    public List<String> getPlayersWithDeposits() {
        List<String> players = new ArrayList<>();
        String sql = "SELECT DISTINCT player_name FROM deposit_records";

        try (PreparedStatement stmt = connection.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                players.add(rs.getString("player_name"));
            }
        } catch (SQLException e) {
            Logger.error("查询玩家列表失败: " + e.getMessage());
        }
        return players;
    }

    @Override
    public int getTotalDeposit(OfflinePlayer player) {
        return getTotalDeposit(player.getName());
    }

    public int getTotalDepositAll() {
        String sql = "SELECT SUM(total_points) AS total FROM player_totals";

        try (PreparedStatement stmt = connection.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                return rs.getInt("total");
            }
        } catch (SQLException e) {
            String errorMsg = "查询全服累计充值失败: " + e.getMessage() +
                    "\nSQL语句: " + sql;
            Logger.error(errorMsg);
        }
        return 0;
    }

    public List<PlayerTotal> getTopPlayers(int page, int perPage) {
        List<PlayerTotal> topPlayers = new ArrayList<>();
        String sql = "SELECT player_name, total_points FROM player_totals " +
                "ORDER BY total_points DESC " +
                "LIMIT ? OFFSET ?";

        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, perPage);
            stmt.setInt(2, (page - 1) * perPage);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String playerName = rs.getString("player_name");
                    int totalPoints = rs.getInt("total_points");
                    topPlayers.add(new PlayerTotal(playerName, totalPoints));
                }
            }
        } catch (SQLException e) {
            Logger.error("查询充值排行榜失败: " + e.getMessage());
        }
        return topPlayers;
    }

    public int getTotalPages(int perPage) {
        String sql = "SELECT COUNT(*) AS total FROM player_totals";

        try (PreparedStatement stmt = connection.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                int totalPlayers = rs.getInt("total");
                return (int) Math.ceil((double) totalPlayers / perPage);
            }
        } catch (SQLException e) {
            Logger.error("查询总页数失败: " + e.getMessage());
        }
        return 1;
    }

    public static class PlayerTotal {
        private final String playerName;
        private final int totalPoints;

        public PlayerTotal(String playerName, int totalPoints) {
            this.playerName = playerName;
            this.totalPoints = totalPoints;
        }

        public String getPlayerName() {
            return playerName;
        }

        public int getTotalPoints() {
            return totalPoints;
        }
    }
}
